from flask import Flask,request, jsonify
from models import db, Data
from config import Config
from datetime import datetime
import re
#from urllib.parse import unquote
from flask_migrate import Migrate  # 添加这行
from flask_cors import CORS  # 新增导入
from schemas import data_schema, datas_schema

app = Flask(__name__)
#CORS(app)  # 默认允许所有域名跨域访问
CORS(app, 
     resources={r"/api/*": {"origins": "http://localhost:8080"}},
     supports_credentials=True,
     allow_headers=["Content-Type", "Authorization"],
     methods=["GET", "POST", "PUT", "DELETE"]
)
app.config.from_object(Config)
db.init_app(app)
migrate = Migrate(app,db)
app.config['SECRET_KEY'] = 'your-secret-key-here'  # 添加这行

@app.route('/api/add_sms', methods=['GET'])
def api_add_sms_get():
    """
    GET方式添加验证码数据
    严格提取被非数字包围的6位连续数字
    示例：/api/add_sms?beizhu=验证码【123456】有效期
    将提取 123456
    """
    raw_beizhu = request.args.get('beizhu', '').strip()
    
    # 使用正则表达式提取严格匹配的6位数字
    # (?<!\d) 表示前面不是数字
    # \d{6}   匹配6位数字
    # (?!\d)  表示后面不是数字
    match = re.search(r'(?<!\d)(\d{6})(?!\d)', raw_beizhu)
    
    if not match:
        return jsonify({
            "status": "error",
            "message": "未找到符合要求的6位数字验证码",
            "hint": "验证码必须是被非数字字符包围的6位连续数字"
        }), 400
    
    clean_beizhu = match.group(1)  # 提取匹配到的6位数字

    try:
        new_data = Data(
            name='验证码',
            yuanbao=1,
            jinbi=1,
            beizhu=clean_beizhu,
            jointime=datetime.now()
        )
        
        db.session.add(new_data)
        db.session.commit()
        
        return jsonify({
            "status": "success",
            "message": "验证码添加成功",
            "data": {
                "id": new_data.id,
                "code": clean_beizhu,
                "source_text": raw_beizhu  # 返回原始文本用于调试
            }
        }), 201
        
    except Exception as e:
        db.session.rollback()
        return jsonify({
            "status": "error",
            "message": f"服务器错误: {str(e)}",
            "received_data": raw_beizhu  # 返回接收到的原始数据
        }), 500

# 1. 查询某个name的所有数据
@app.route('/api/data/<name>', methods=['GET'])
def get_all_by_name(name):
    records = Data.query.filter_by(name=name).all()
    return jsonify(datas_schema.dump(records))

# 2. 获取某个name的最新记录
@app.route('/api/data/latest/<name>', methods=['GET'])
def get_latest_by_name(name):
    record = Data.query.filter_by(name=name).order_by(Data.jointime.desc()).first()
    return jsonify(data_schema.dump(record))

# 3. 新增数据
@app.route('/api/data', methods=['POST'])
def add_data():
    print("请求头:", request.headers)  # 查看请求头
    print("原始数据:", request.data)   # 查看原始请求体
    data = request.json
    print(data)
    new_data = Data(
        name=data['name'],
        yuanbao=data.get('yuanbao'),
        jinbi=data.get('jinbi'),
        beizhu=data.get('beizhu')
    )
    db.session.add(new_data)
    db.session.commit()
    return jsonify(data_schema.dump(new_data)), 201

# 4. 删除某个name的所有数据
@app.route('/api/data/<name>', methods=['DELETE'])
def delete_all_by_name(name):
    Data.query.filter_by(name=name).delete()
    db.session.commit()
    return jsonify({'message': f'Deleted all records for {name}'})

# 5. 删除单条数据
@app.route('/api/data/id/<int:id>', methods=['DELETE'])
def delete_by_id(id):
    record = Data.query.get_or_404(id)
    db.session.delete(record)
    db.session.commit()
    return jsonify({'message': f'Deleted record {id}'})

# 6. 修改单条数据
@app.route('/api/data/id/<int:id>', methods=['PUT'])
def update_data(id):
    record = Data.query.get_or_404(id)
    data = request.json
    record.name = data.get('name', record.name)
    record.yuanbao = data.get('yuanbao', record.yuanbao)
    record.jinbi = data.get('jinbi', record.jinbi)
    record.beizhu = data.get('beizhu', record.beizhu)
    db.session.commit()
    return jsonify(data_schema.dump(record))

# 7. 模糊查询
@app.route('/api/search', methods=['GET'])
def search():
    keyword = request.args.get('q', '')
    results = Data.query.filter(Data.name.ilike(f'%{keyword}%')).all()
    return jsonify(datas_schema.dump(results))

# 8. 获取所有不同name的最新记录
@app.route('/api/data/latest-all', methods=['GET'])
def get_all_latest():
    #subquery = db.session.query(
    #    Data.name,
    #    db.func.max(Data.jointime).label('max_time')
    #).group_by(Data.name).subquery()
    #
    #results = Data.query.join(
    #    subquery,
    #    (Data.name == subquery.c.name) & 
    #    (Data.jointime == subquery.c.max_time)
    #).all()
    subquery = db.session.query(Data.name,db.func.max(Data.id).label('max_id')).group_by(Data.name).subquery()
    results = Data.query.join(subquery,(Data.name == subquery.c.name)&(Data.id == subquery.c.max_id)).all()
    return jsonify(datas_schema.dump(results))

# 9. 自定义SQL执行（危险！仅测试用）
@app.route('/api/execute', methods=['POST'])
def execute_sql():
    if not request.json.get('sql'):
        return jsonify({'error': 'Missing SQL'}), 400
    
    try:
        result = db.session.execute(request.json['sql'])
        if result.returns_rows:
            return jsonify([dict(row) for row in result])
        return jsonify({'affected_rows': result.rowcount})
    except Exception as e:
        return jsonify({'error': str(e)}), 500




if __name__ == '__main__':
    app.run(debug=true)
